﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using QLVatTu.Class;
using System.Globalization;

namespace QLVatTu.NghiepVu
{
    public partial class frmPhieuYeuCauNhap : Form
    {
        KetNoiDB cn = new KetNoiDB();
        public string MaPhieuYeuCau = "";
        public string PhieuDuTru = "";

        public frmPhieuYeuCauNhap()
        {
            InitializeComponent();
        }

        private void loadData()
        {
            cbbCongTrinhDuAn.DataSource = cn.getTable("select * from tbl_CongTrinhDuAn");
            cbbCongTrinhDuAn.DisplayMember = "TenCongTrinhDuAn";
            cbbCongTrinhDuAn.ValueMember = "MaCongTrinhDuAn";

            cbbPheDuyet.DataSource = cn.CreateTrangThai();
            cbbPheDuyet.DisplayMember = "TenTrangThai";
            cbbPheDuyet.ValueMember = "TrangThai";

            DataTable dt = new DataTable();

            //load cbbPhieuDuTru
            if (PhieuDuTru == "")
            {
                dt = cn.getTable("select SoPhieu from tbl_PhieuDuTru where TrangThai=1 and SoPhieu NOT IN (SELECT DISTINCT MaPhieuDuTru FROM  dbo.tbl_PhieuYeuCauNhap)");
            }
            else
            {
                dt = cn.getTable("select SoPhieu from tbl_PhieuDuTru where SoPhieu='" + PhieuDuTru + "'");
            }

            DataRow dr = dt.NewRow();
            dr["SoPhieu"] = "---Danh sách phiếu---";
            dt.Rows.InsertAt(dr, 0);
            cbbPhieuDuTru.DataSource = dt;
            cbbPhieuDuTru.DisplayMember = "SoPhieu";
            cbbPhieuDuTru.ValueMember = "SoPhieu";

            string sql = "";
            if (MaPhieuYeuCau == "")
            {
                //Thêm
                txtSoPhieu.Text = cn.SinhMa("SoPhieu", "tbl_PhieuYeuCauNhap", "YC");
            }
            else
            {
                //Sửa
                txtSoPhieu.Text = MaPhieuYeuCau;
                cbbPhieuDuTru.Enabled = false;
                sql = @"select * from tbl_PhieuYeuCauNhap where SoPhieu='" + MaPhieuYeuCau + "'";
                dt = cn.getTable(sql);
                if (dt.Rows.Count > 0)
                {
                    dtpNgayLap.Value = DateTime.Parse(dt.Rows[0]["NgayLap"].ToString());
                    cbbCongTrinhDuAn.SelectedValue = dt.Rows[0]["MaCongTrinhDuAn"].ToString();
                    txtNhanVien.Text = dt.Rows[0]["NguoiLap"].ToString();
                    cbbPheDuyet.SelectedValue = dt.Rows[0]["TrangThai"].ToString();

                    string MaPhieuDuTru = dt.Rows[0]["MaPhieuDuTru"].ToString();
                    cbbPhieuDuTru.Text = MaPhieuDuTru.ToString();

                    //load dgvVatTuDuTru theo MaPhieuDuTru
                    load_PhieuDuTru(MaPhieuDuTru);

                    //load dgvChiTietPhieuYeuCauNhap
                    int stt = 0;
                    float SoLuongTonKho = 0;

                    sql = @"SELECT     dbo.tbl_ChiTietPhieuYeuCauNhap.MaVatTu, dbo.tbl_VatTu.TenVatTu, dbo.tbl_ChiTietPhieuYeuCauNhap.MaDonViTinh, dbo.tbl_DonViTinh.TenDonViTinh, 
                                                  dbo.tbl_ChiTietPhieuYeuCauNhap.SoLuong, dbo.tbl_PhieuYeuCauNhap.MaPhieuDuTru
                            FROM         dbo.tbl_ChiTietPhieuYeuCauNhap INNER JOIN
                                                  dbo.tbl_VatTu ON dbo.tbl_ChiTietPhieuYeuCauNhap.MaVatTu = dbo.tbl_VatTu.MaVatTu INNER JOIN
                                                  dbo.tbl_DonViTinh ON dbo.tbl_ChiTietPhieuYeuCauNhap.MaDonViTinh = dbo.tbl_DonViTinh.MaDonViTinh INNER JOIN
                                                  dbo.tbl_PhieuYeuCauNhap ON dbo.tbl_ChiTietPhieuYeuCauNhap.SoPhieu = dbo.tbl_PhieuYeuCauNhap.SoPhieu
                            WHERE     dbo.tbl_ChiTietPhieuYeuCauNhap.SoPhieu = '" + MaPhieuYeuCau + "'";
                    DataTable dtChiTiet = cn.getTable(sql);
                    if (dtChiTiet.Rows.Count > 0)
                    {
                        foreach (DataRow drct in dtChiTiet.Rows)
                        {
                            stt++;
                            DataGridViewRow dgvRowPYC = (DataGridViewRow)dgvChiTietPhieuYeuCauNhap.Rows[0].Clone();
                            dgvRowPYC.Cells[1].Value = stt;
                            dgvRowPYC.Cells[2].Value = drct["TenVatTu"].ToString();
                            dgvRowPYC.Cells[3].Value = drct["TenDonViTinh"].ToString();

                            float SoLuongDeNghi = 0;
                            sql = @"SELECT     SoLuong
                                    FROM         dbo.tbl_ChiTietPhieuDuTru
                                    WHERE     SoPhieu = '" + drct["MaPhieuDuTru"].ToString() + "' AND MaVatTu = '" + drct["MaVatTu"].ToString() + @"'
                                    GROUP BY SoLuong";
                            dt = cn.getTable(sql);
                            if (dt.Rows.Count > 0) SoLuongDeNghi = float.Parse(dt.Rows[0]["SoLuong"].ToString());
                            dgvRowPYC.Cells[4].Value = SoLuongDeNghi;

                            sql = "select sum(SoLuong) as SoLuongTonKho from tbl_LuuKho where MaVatTu='" + drct["MaVatTu"].ToString() + "' group by MaVatTu";
                            DataTable dtSoLuong = cn.getTable(sql);
                            if (dtSoLuong.Rows.Count > 0) SoLuongTonKho = float.Parse(dtSoLuong.Rows[0]["SoLuongTonKho"].ToString());

                            dgvRowPYC.Cells[5].Value = SoLuongTonKho;

                            double SoLuongConThieu = Math.Round((SoLuongDeNghi - SoLuongTonKho),2);
                            dgvRowPYC.Cells[6].Value = SoLuongConThieu;

                            dgvRowPYC.Cells[7].Value = drct["SoLuong"].ToString();
                            dgvRowPYC.Cells[8].Value = drct["MaVatTu"].ToString();
                            dgvRowPYC.Cells[9].Value = drct["MaDonViTinh"].ToString();
                            dgvChiTietPhieuYeuCauNhap.Rows.Add(dgvRowPYC);
                        }
                    }
                }

                foreach (DataGridViewRow dgvr in dgvChiTietPhieuYeuCauNhap.Rows)
                {
                    if (dgvr.IsNewRow) continue;
                    dgvr.Cells[0].Value = ckbChonHet.Checked;
                }
            }
        }

        private void frmPhieuYeuCauNhap_Load(object sender, EventArgs e)
        {
            if (cn.KetNoi())
            {
                loadData();
            }
        }

        private void load_PhieuDuTru(string MaPhieuDuTru)
        {
            //load cbbCongTrinhDuAn theo phiếu dự trù
            cbbCongTrinhDuAn.SelectedValue = cn.LayDuLieu("select MaCongTrinhDuAn from tbl_PhieuDuTru where SoPhieu='" + MaPhieuDuTru + "'");

            //load phiếu dự trù
            string sql = @"SELECT  row_number() over(order by tbl_ChiTietPhieuDuTru.SoPhieu) as STT, dbo.tbl_VatTu.TenVatTu, dbo.tbl_DonViTinh.TenDonViTinh, dbo.tbl_ChiTietPhieuDuTru.SoLuong, dbo.tbl_ChiTietPhieuDuTru.MaVatTu, 
                                                  dbo.tbl_ChiTietPhieuDuTru.MaDonViTinh
                            FROM         dbo.tbl_ChiTietPhieuDuTru INNER JOIN
                                                  dbo.tbl_DonViTinh ON dbo.tbl_ChiTietPhieuDuTru.MaDonViTinh = dbo.tbl_DonViTinh.MaDonViTinh INNER JOIN
                                                  dbo.tbl_VatTu ON dbo.tbl_ChiTietPhieuDuTru.MaVatTu = dbo.tbl_VatTu.MaVatTu
                            WHERE tbl_ChiTietPhieuDuTru.SoPhieu='" + MaPhieuDuTru + "'";
            DataTable tbl = cn.getTable(sql);
            dgvVatTuDuTru.Rows.Clear();
            ckbChonHet.Checked = false;
            dgvChiTietPhieuYeuCauNhap.Rows.Clear();
            if (tbl.Rows.Count > 0)
            {
                foreach (DataRow dr in tbl.Rows)
                {
                    DataGridViewRow dtgRow = (DataGridViewRow)dgvVatTuDuTru.Rows[0].Clone();
                    dtgRow.Cells[1].Value = dr["STT"].ToString();
                    dtgRow.Cells[2].Value = dr["TenVatTu"].ToString();
                    dtgRow.Cells[3].Value = dr["TenDonViTinh"].ToString();
                    dtgRow.Cells[4].Value = dr["SoLuong"].ToString();
                    dtgRow.Cells[5].Value = dr["MaVatTu"].ToString();
                    dtgRow.Cells[6].Value = dr["MaDonViTinh"].ToString();
                    dgvVatTuDuTru.Rows.Add(dtgRow);
                }
            }
            foreach (DataGridViewRow dgvr in dgvVatTuDuTru.Rows)
            {
                if (dgvr.IsNewRow) continue;
                dgvr.Cells[0].Value = ckbChonHet.Checked;
            }
        }

        private void cbbPhieuDuTru_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbbPhieuDuTru.SelectedValue != null)
            {
                load_PhieuDuTru(cbbPhieuDuTru.SelectedValue.ToString());
            }
            else
            {
                dgvVatTuDuTru.Rows.Clear();
            }
        }

        private void btnThoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void load_ChiTietPhieuYeuCau()
        {
            dgvChiTietPhieuYeuCauNhap.Rows.Clear();
            int stt = 0;
            string sql = "";
            double SoLuongTonKho = 0;
            float SoLuongDuTru = 0;
            double SoLuongCanNhap = 0;

            DataTable dt = new DataTable();

            foreach (DataGridViewRow dgvr in dgvVatTuDuTru.Rows)
            {
                if (dgvr.IsNewRow) continue;
                if (dgvr.Cells[0].Value != null && bool.Parse(dgvr.Cells[0].Value.ToString()) == true && dgvr.Cells["VatTu"].Value != null)
                {
                    stt++;
                    DataGridViewRow dgvrPYC = (DataGridViewRow)dgvChiTietPhieuYeuCauNhap.Rows[0].Clone();
                    dgvrPYC.Cells[1].Value = stt;
                    dgvrPYC.Cells[2].Value = dgvr.Cells["VatTu"].Value.ToString();
                    dgvrPYC.Cells[3].Value = dgvr.Cells["DonViTinh"].Value.ToString();
                    dgvrPYC.Cells[4].Value = dgvr.Cells["SoLuongDuTru"].Value.ToString();

                    //Tính số lượng tồn kho
                    sql = "select sum(SoLuong) as SoLuongTonKho from tbl_LuuKho where MaVatTu='" + dgvr.Cells["MaVatTu"].Value.ToString() + "' group by MaVatTu";
                    dt = cn.getTable(sql);
                    if (dt.Rows.Count > 0) SoLuongTonKho = float.Parse(dt.Rows[0]["SoLuongTonKho"].ToString());

                    dgvrPYC.Cells[5].Value = SoLuongTonKho;

                    SoLuongDuTru = float.Parse(dgvr.Cells["SoLuongDuTru"].Value.ToString());

                    SoLuongCanNhap = Math.Round((SoLuongDuTru - SoLuongTonKho),2);

                    if (SoLuongCanNhap <= 0)
                    {
                        continue;
                    }
                    else
                    {
                        dgvrPYC.Cells[6].Value = SoLuongCanNhap;
                        dgvrPYC.Cells[7].Value = SoLuongCanNhap;
                    }

                    dgvrPYC.Cells[8].Value = dgvr.Cells["MaVatTu"].Value.ToString();
                    dgvrPYC.Cells[9].Value = dgvr.Cells["MaDonViTinh"].Value.ToString();
                    dgvChiTietPhieuYeuCauNhap.Rows.Add(dgvrPYC);

                    foreach (DataGridViewRow dgvrCTYC in dgvChiTietPhieuYeuCauNhap.Rows)
                    {
                        if (dgvrCTYC.IsNewRow) continue;
                        dgvrCTYC.Cells[0].Value = false;
                    }
                }
            }
        }

        private bool IsValid()
        {
            bool kt = true;

           

            if (txtNhanVien.Text == "")
            {
                MessageBox.Show("Bạn chưa nhập tên nhân viên.", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txtNhanVien.Focus();
                kt= false;
            }

            else if (dgvVatTuDuTru.Rows.Count == 1)
            {
                MessageBox.Show("Bạn chưa chọn nhập thông tin vật tư.", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                dgvVatTuDuTru.Focus();
                kt = false;
            }
            else
            {
                if (cbbPhieuDuTru.SelectedIndex > 0)
                {
                    string sql = "select NgayDuTru from tbl_PhieuDuTru where SoPhieu='" + cbbPhieuDuTru.SelectedValue.ToString() + "'";
                    DateTime dtNgayDuTru = DateTime.Parse(cn.LayDuLieu(sql));

                    if (dtpNgayLap.Value < dtNgayDuTru || dtpNgayLap.Value > DateTime.Now)
                    {
                        MessageBox.Show("Ngày lập phiếu yêu cầu không nhỏ hơn ngày dự trù: " + dtNgayDuTru.ToString("dd/MM/yyyy") + " hoặc lớn hơn ngày hiện tại", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        dtpNgayLap.Focus();
                        kt = false;
                    }
                }
            }

            foreach (DataGridViewRow dgvr in dgvChiTietPhieuYeuCauNhap.Rows)
            {
                if (dgvr.IsNewRow) continue;

                if (dgvr.Cells["SoLuongCT"].Value == null)
                {
                    dgvr.Cells["SoLuongCT"].ErrorText = "Lỗi: " + dgvChiTietPhieuYeuCauNhap.Columns[dgvr.Cells["SoLuongCT"].ColumnIndex].HeaderText + " chưa được nhập ";
                    kt = false;
                }
                else
                {
                    string strSoLuongCT;
                    double SoLuongCT = 0;
                    string strSoLuongConThieu;
                    double SoLuongConThieu = 0;
                    string strSoLuongTonKho;
                    double SoLuongTonKho = 0;

                    if (dgvr.Cells["SoLuongCT"].Value.ToString().Contains(','))
                    {
                        strSoLuongCT = dgvr.Cells["SoLuongCT"].Value.ToString().Replace(',', '.');
                    }
                    else
                    {
                        strSoLuongCT = dgvr.Cells["SoLuongCT"].Value.ToString();
                    }
                    double.TryParse(strSoLuongCT, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out SoLuongCT);

                    if (dgvr.Cells["SoLuongTonKho"].Value.ToString().Contains(','))
                    {
                        strSoLuongTonKho = dgvr.Cells["SoLuongTonKho"].Value.ToString().Replace(',', '.');
                    }
                    else
                    {
                        strSoLuongTonKho = dgvr.Cells["SoLuongTonKho"].Value.ToString();
                    }
                    double.TryParse(strSoLuongTonKho, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out SoLuongTonKho);

                    if (dgvr.Cells["SoLuongConThieu"].Value.ToString().Contains(','))
                    {
                        strSoLuongConThieu = dgvr.Cells["SoLuongConThieu"].Value.ToString().Replace(',', '.');
                    }
                    else
                    {
                        strSoLuongConThieu = dgvr.Cells["SoLuongConThieu"].Value.ToString();
                    }
                    double.TryParse(strSoLuongConThieu, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out SoLuongConThieu);

                    if (SoLuongCT <= 0)
                    {
                        dgvr.Cells["SoLuongCT"].ErrorText = "Lỗi: " + dgvChiTietPhieuYeuCauNhap.Columns[dgvr.Cells["SoLuongCT"].ColumnIndex].HeaderText + " không được nhỏ hơn hoặc bằng 0 ";
                        kt = false;
                    }

                    else if (SoLuongCT > SoLuongConThieu)
                    {
                        dgvr.Cells["SoLuongCT"].ErrorText = "Lỗi: " + dgvChiTietPhieuYeuCauNhap.Columns[dgvr.Cells["SoLuongCT"].ColumnIndex].HeaderText + " không được lớn hơn số lượng dự trù";
                        kt = false;
                    }
                    else
                    {
                        dgvr.Cells["SoLuongCT"].ErrorText = "";
                    }
                }
            }
            return kt;
        }

        private void btnLuu_Click(object sender, EventArgs e)
        {
            if (IsValid())
            {
                string SoPhieu = txtSoPhieu.Text;
                string NgayLap = dtpNgayLap.Value.ToString("yyyy-MM-dd");
                string MaCongTrinhDuAn = cbbCongTrinhDuAn.SelectedValue.ToString();
                string NguoiLap = txtNhanVien.Text;
                string MaPhieuDuTru = cbbPhieuDuTru.SelectedValue.ToString();
                string TrangThai = cbbPheDuyet.SelectedValue.ToString();

                if (MaPhieuYeuCau == "")
                {
                    cn.Insert_Table("tbl_PhieuYeuCauNhap", "SoPhieu,NgayLap,NguoiLap,MaCongTrinhDuAn,MaPhieuDuTru,TrangThai", "'" + SoPhieu + "','" + NgayLap + "',N'" + NguoiLap + "','" + MaCongTrinhDuAn + "','" + MaPhieuDuTru + "','" + TrangThai + "'");
                    foreach (DataGridViewRow dgvr in dgvChiTietPhieuYeuCauNhap.Rows)
                    {
                        if (dgvr.IsNewRow) continue;

                        string strSoLuong;
                        double SoLuong = 0;

                        if (dgvr.Cells["SoLuongCT"].Value.ToString().Contains(','))
                        {
                            strSoLuong = dgvr.Cells["SoLuongCT"].Value.ToString().Replace(',', '.');
                        }
                        else
                        {
                            strSoLuong = dgvr.Cells["SoLuongCT"].Value.ToString();
                        }

                        double.TryParse(strSoLuong, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out SoLuong);

                        cn.Insert_Table("tbl_ChiTietPhieuYeuCauNhap", "SoPhieu,MaVatTu,MaDonViTinh,SoLuong", "'" + SoPhieu + "','" + dgvr.Cells["MaVatTuCT"].Value.ToString() + "','" + dgvr.Cells["MaDonViTinhCT"].Value.ToString() + "','" + SoLuong.ToString().Replace(',', '.') + "'");
                    }
                }
                else
                {
                    cn.Update_Table("tbl_PhieuYeuCauNhap", "NgayLap='" + NgayLap + "',NguoiLap=N'" + NguoiLap + "',TrangThai='" + TrangThai + "'", "SoPhieu='" + SoPhieu + "'");
                    cn.Delete_Table("tbl_ChiTietPhieuYeuCauNhap", "SoPhieu='" + SoPhieu + "'");

                    foreach (DataGridViewRow dgvr in dgvChiTietPhieuYeuCauNhap.Rows)
                    {
                        if (dgvr.IsNewRow) continue;

                        string strSoLuong;
                        double SoLuong = 0;

                        if (dgvr.Cells["SoLuongCT"].Value.ToString().Contains(','))
                        {
                            strSoLuong = dgvr.Cells["SoLuongCT"].Value.ToString().Replace(',', '.');
                        }
                        else
                        {
                            strSoLuong = dgvr.Cells["SoLuongCT"].Value.ToString();
                        }

                        double.TryParse(strSoLuong, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out SoLuong);

                        cn.Insert_Table("tbl_ChiTietPhieuYeuCauNhap", "SoPhieu,MaVatTu,MaDonViTinh,SoLuong", "'" + SoPhieu + "','" + dgvr.Cells["MaVatTuCT"].Value.ToString() + "','" + dgvr.Cells["MaDonViTinhCT"].Value.ToString() + "','" + SoLuong.ToString().Replace(',', '.') + "'");
                    }
                }
                this.Close();
            }
        }

        private void dgvVatTuDuTru_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (dgvVatTuDuTru.SelectedRows.Count >= 0 && e.ColumnIndex == 0)
            {
                if (!dgvVatTuDuTru.Rows[e.RowIndex].IsNewRow && dgvVatTuDuTru.Rows[e.RowIndex].Cells[0].Value != null)
                {
                    bool Checked = bool.Parse(dgvVatTuDuTru.Rows[e.RowIndex].Cells[0].Value.ToString());
                    dgvVatTuDuTru.Rows[e.RowIndex].Cells[0].Value = !Checked;
                    dgvVatTuDuTru.EndEdit();
                }
            }
        }

        private void ckbChonHet_Click(object sender, EventArgs e)
        {
            if (dgvVatTuDuTru.RowCount > 0)
            {
                foreach (DataGridViewRow dgvr in dgvVatTuDuTru.Rows)
                {
                    if (dgvr.IsNewRow) continue;
                    dgvr.Cells[0].Value = ckbChonHet.Checked;
                }
                dgvVatTuDuTru.EndEdit();
            }
        }

        private void btnChon_Click(object sender, EventArgs e)
        {
            load_ChiTietPhieuYeuCau();
        }

        private void btnXoaChon_Click(object sender, EventArgs e)
        {
            for (int i = dgvChiTietPhieuYeuCauNhap.Rows.Count - 1; i >= 0; i--)
            {
                if (dgvChiTietPhieuYeuCauNhap.Rows[i].Cells[0].Value != null && (bool)dgvChiTietPhieuYeuCauNhap.Rows[i].Cells[0].Value == true)
                {
                    dgvChiTietPhieuYeuCauNhap.Rows.RemoveAt(i);
                }
            }
        }

        private void dgvChiTietPhieuYeuCauNhap_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex != -1 && e.ColumnIndex == 0)
            {
                if (!dgvChiTietPhieuYeuCauNhap.Rows[e.RowIndex].IsNewRow && dgvChiTietPhieuYeuCauNhap.Rows[e.RowIndex].Cells[0].Value != null)
                {
                    bool Checked = bool.Parse(dgvChiTietPhieuYeuCauNhap.Rows[e.RowIndex].Cells[0].Value.ToString());
                    dgvChiTietPhieuYeuCauNhap.Rows[e.RowIndex].Cells[0].Value = !Checked;
                    dgvChiTietPhieuYeuCauNhap.EndEdit();
                }
            }
        }
    }
}
